using System;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    /// <summary>
    /// DbObject is the class from which all classes in the Data Services
    /// Tier inherit. The core functionality of establishing a connection
    /// with the database and executing simple stored procedures is also
    /// provided by this base class.
    /// </summary>
    public abstract class DbObject
    {
        public DbObject()
        {
        }
        private SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters, SqlConnection ConnectionView)
        {
            SqlCommand command = BuildQueryCommand(storedProcName, parameters, ConnectionView);

            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int,
                4, /* Size */
                ParameterDirection.ReturnValue,
                false, /* is nullable */
                0, /* byte precision */
                0, /* byte scale */
                string.Empty,
                DataRowVersion.Default,
                null));

            return command;
        }
        private SqlCommand BuildIntCommand(string storedProcName, SqlConnection ConnectionView)
        {
            SqlCommand command = BuildQueryCommand(storedProcName, ConnectionView);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int,
                4, /* Size */
                ParameterDirection.ReturnValue,
                false, /* is nullable */
                0, /* byte precision */
                0, /* byte scale */
                string.Empty,
                DataRowVersion.Default,
                null));
            return command;
        }
        private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters, SqlConnection ConnectionView)
        {
            SqlCommand command = new SqlCommand(storedProcName, ConnectionView);
            command.CommandType = CommandType.StoredProcedure;


            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }

            return command;

        }
        private SqlCommand BuildQueryCommand(string storedProcName, SqlConnection ConnectionView)
        {
            SqlCommand command = new SqlCommand(storedProcName, ConnectionView);
            command.CommandType = CommandType.StoredProcedure;
            return command;
        }
        protected int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected, SqlConnection ConnectionView)
        {
            int result;
            if (ConnectionView.State != ConnectionState.Open)
            ConnectionView.Open();
            SqlCommand command = BuildIntCommand(storedProcName, parameters, ConnectionView);
            rowsAffected = command.ExecuteNonQuery();
            result = (int)command.Parameters["ReturnValue"].Value;
            ConnectionView.Close();
            return result;
        }
        protected SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters, SqlConnection ConnectionView)
        {
            SqlDataReader returnReader;

            ConnectionView.Open();
            SqlCommand command = BuildQueryCommand(storedProcName, parameters, ConnectionView);
            command.CommandType = CommandType.StoredProcedure;

            returnReader = command.ExecuteReader();
            //Connection.Close();
            return returnReader;
        }
        protected DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, SqlConnection ConnectionView)
        {
            DataSet dataSet = new DataSet();
            ConnectionView.Open();
            SqlDataAdapter sqlDA = new SqlDataAdapter();
            sqlDA.SelectCommand = BuildQueryCommand(storedProcName, parameters, ConnectionView);
            sqlDA.Fill(dataSet, tableName);
            ConnectionView.Close();

            return dataSet;
        }
        protected void RunProcedure(string storedProcName, IDataParameter[] parameters, DataSet dataSet, string tableName, SqlConnection ConnectionView)
        {
            ConnectionView.Open();
            SqlDataAdapter sqlDA = new SqlDataAdapter();
            sqlDA.SelectCommand = BuildIntCommand(storedProcName, parameters, ConnectionView);
            sqlDA.Fill(dataSet, tableName);
            ConnectionView.Close();
        }
        protected DataSet RunProcedure(string storedProcName, string tableName, SqlConnection ConnectionView)
        {
            DataSet dataSet = new DataSet();
            ConnectionView.Open();
            SqlDataAdapter sqlDA = new SqlDataAdapter();
            sqlDA.SelectCommand = BuildQueryCommand(storedProcName, ConnectionView);
            sqlDA.Fill(dataSet, tableName);
            ConnectionView.Close();
            return dataSet;
        }

    }
}